from ..database import connect_to_database, close_database_connection


def get_user_calories(user_id, date):
    db, ssh_tunnel = connect_to_database()

    # 查询用户热量记录的 SQL
    select_query = """
    SELECT total_intake, target_intake, protein, fat, carbs
    FROM user_calories
    WHERE date = %s AND user_id = %s
    """

    # 插入初始化记录的 SQL
    insert_query = """
    INSERT INTO user_calories (user_id, date, total_intake, target_intake, protein, fat, carbs)
    VALUES (%s, %s, %s, %s, %s, %s, %s)
    """

    try:
        with db.cursor() as cursor:
            # 查询记录
            cursor.execute(select_query, (date, user_id))
            result = cursor.fetchone()

            # 如果没有记录，插入初始化数据
            if not result:
                cursor.execute(
                    insert_query,
                    (
                        user_id,
                        date,
                        0,
                        2000,
                        0.0,
                        0.0,
                        0.0,
                    ),  # 默认目标摄入热量 2000 千卡，其余为 0
                )
                db.commit()  # 提交事务

                # 返回初始化的数据
                return {
                    "totalIntake": 0,
                    "targetIntake": 2000,
                    "protein": 0,
                    "fat": 0,
                    "carbs": 0,
                }

            # 如果有记录，返回查询结果
            return {
                "totalIntake": result[0],
                "targetIntake": result[1],
                "protein": result[2],
                "fat": result[3],
                "carbs": result[4],
            }
    except Exception as e:
        raise Exception(f"Database query error: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)


def add_food_to_meal(user_id, record):
    """
    添加食物到用户的餐次记录，并更新用户的总热量统计。
    """
    query_get_food_id = "SELECT id FROM foods WHERE name = %s"
    query_insert_meal_record = """
    INSERT INTO meal_records (
        user_id, food_id, meal_type, entry_time, weight, calories, protein, fat, carbs
    )
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    query_update_user_calories = """
    INSERT INTO user_calories (
        user_id, date, total_intake, protein, fat, carbs
    )
    VALUES (%s, %s, %s, %s, %s, %s)
    ON DUPLICATE KEY UPDATE
        total_intake = total_intake + VALUES(total_intake),
        protein = protein + VALUES(protein),
        fat = fat + VALUES(fat),
        carbs = carbs + VALUES(carbs)
    """
    db, ssh_tunnel = connect_to_database()
    try:
        with db.cursor() as cursor:
            # 查询食物ID
            print(f"Querying food ID for: {record.food_name}")
            cursor.execute(query_get_food_id, (record.food_name,))
            result = cursor.fetchone()
            if result is None:
                print(f"食物 '{record.food_name}' 未找到，请检查数据库内容")
                raise Exception(f"未找到名称为 '{record.food_name}' 的食物")
            food_id = result[0]

            print(f"record: {record}")
            print(f"food_id: {food_id}")

            # 插入餐次记录
            print(f"Executing query: {query_insert_meal_record}")
            print(
                f"With parameters: {(user_id, food_id, record.meal_type, record.entry_time, record.weight, record.calories, record.protein, record.fat, record.carbs)}"
            )
            cursor.execute(
                query_insert_meal_record,
                (
                    user_id,
                    food_id,
                    record.meal_type,
                    record.entry_time,
                    record.weight,
                    record.calories,
                    record.protein,
                    record.fat,
                    record.carbs,
                ),
            )

            # 更新用户总热量统计
            date = record.entry_time.date()  # 从 entry_time 提取日期
            print(f"Updating user_calories for user_id: {user_id} on date: {date}")
            cursor.execute(
                query_update_user_calories,
                (
                    user_id,
                    date,
                    record.calories,
                    record.protein,
                    record.fat,
                    record.carbs,
                ),
            )

            db.commit()
            return {
                "message": f"成功添加食物 ('{record.food_name}', ID: {food_id}) 到餐次 '{record.meal_type}' 中，并更新用户总热量统计。"
            }
    except Exception as e:
        db.rollback()
        print(f"添加食物到餐次失败: {e}")
        raise
    finally:
        close_database_connection(db, ssh_tunnel)


def get_meals_by_date(user_id, date):
    """
    获取指定用户在某一天的用餐信息，按早餐、午餐、晚餐、加餐分类。

    :param user_id: 用户的唯一ID
    :param date: 查询日期，格式为 'YYYY-MM-DD'
    :return: 用餐信息字典 {餐次类型: [菜品列表]} 或 None
    """
    db, ssh_tunnel = connect_to_database()
    query = """
    SELECT 
        m.meal_type,
        f.name AS food_name,
        m.weight AS food_weight,
        m.calories AS food_calories,
        m.protein AS food_protein,
        m.fat AS food_fat,
        m.carbs AS food_carbs
    FROM 
        meal_records m
    JOIN 
        foods f 
    ON 
        m.food_id = f.id
    WHERE 
        m.user_id = %s 
        AND DATE(m.entry_time) = %s
    ORDER BY 
        FIELD(m.meal_type, '早餐', '午餐', '晚餐', '加餐'), m.entry_time
    """
    try:
        with db.cursor() as cursor:
            cursor.execute(query, (user_id, date))
            results = cursor.fetchall()

            if not results:
                return None

            # 构造按餐次分类的用餐信息字典
            meals_data = {}
            for meal_type, food_name, weight, calories, protein, fat, carbs in results:
                food_info = {
                    "name": food_name,
                    "weight": weight,
                    "calories": calories,
                    "protein": protein,
                    "fat": fat,
                    "carbs": carbs,
                }
                if meal_type not in meals_data:
                    meals_data[meal_type] = []
                meals_data[meal_type].append(food_info)

            return meals_data
    except Exception as e:
        raise Exception(f"Database query error: {e}")
    finally:
        close_database_connection(db, ssh_tunnel)
